"""
Case Type   : ustore_DQL_SET_OPERATIONS
Case Name   : 验证except与inner join结合
Create At   : 2022-05-12
Owner       : nanyang12
Description :
    1.创建表
    2.使用copy命令插入数据
    3.创建索引
    4.except与inner join结合
    5.环境清理
Expect      :
    1.创建成功
    2.数据插入成功
    3.创建成功
    4.成功
    5.清理成功
History     :
"""

import os
import subprocess
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class Ustore01(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_root = Node('PrimaryRoot')
        self.com = Common()
        self.constant = Constant()
        self.table_name = 't_ustore_b50_add'
        self.index_name = 'i_ustore_b50_add'
        self.copy_file1 = 'row_col_01_33.txt'
        self.copy_file2 = 'row_col_02_33.txt'
        self.copy_file3 = 'row_col_03_33.txt'
        self.copy_file4 = 'row_col_04_33.txt'
        self.copy_file5 = 'row_col_05_33.txt'
        self.copy_file6 = 'row_col_06_33.txt'
        self.copy_file7 = 'row_col_07_33.txt'
        self.copy_file8 = 'row_col_08_33.txt'
        self.cur_path_1 = os.path.join(macro.SCRIPTS_PATH, self.copy_file1)
        self.cur_path_2 = os.path.join(macro.SCRIPTS_PATH, self.copy_file2)
        self.cur_path_3 = os.path.join(macro.SCRIPTS_PATH, self.copy_file3)
        self.cur_path_4 = os.path.join(macro.SCRIPTS_PATH, self.copy_file4)
        self.cur_path_5 = os.path.join(macro.SCRIPTS_PATH, self.copy_file5)
        self.cur_path_6 = os.path.join(macro.SCRIPTS_PATH, self.copy_file6)
        self.cur_path_7 = os.path.join(macro.SCRIPTS_PATH, self.copy_file7)
        self.cur_path_8 = os.path.join(macro.SCRIPTS_PATH, self.copy_file8)
        self.parent_path = os.path.dirname(macro.DB_INSTANCE_PATH)
        self.target_path1 = os.path.join(self.parent_path, self.copy_file1)
        self.target_path2 = os.path.join(self.parent_path, self.copy_file2)
        self.target_path3 = os.path.join(self.parent_path, self.copy_file3)
        self.target_path4 = os.path.join(self.parent_path, self.copy_file4)
        self.target_path5 = os.path.join(self.parent_path, self.copy_file5)
        self.target_path6 = os.path.join(self.parent_path, self.copy_file6)
        self.target_path7 = os.path.join(self.parent_path, self.copy_file7)
        self.target_path8 = os.path.join(self.parent_path, self.copy_file8)

    def test_join(self):
        step_txt = '-----step1:创建表;expect: 创建成功-----'
        self.log.info(step_txt)
        cmd_sql = f"""
        drop table if exists {self.table_name}_01 cascade;
        drop table if exists {self.table_name}_02 cascade;
        drop table if exists {self.table_name}_03 cascade;
        drop table if exists {self.table_name}_04 cascade;
        drop table if exists {self.table_name}_05 cascade;
        drop table if exists {self.table_name}_06 cascade;
        drop table if exists {self.table_name}_07 cascade;
        drop table if exists {self.table_name}_08 cascade;
            CREATE TABLE {self.table_name}_01 (
                c_id int,
                c_d_id numeric,
                c_w_id int,
                c_first varchar(16),
                c_middle char(2),
                c_last varchar(16),
                c_street_1 text,
                c_street_2 varchar(60),
                c_city varchar(60),
                c_state char(2),
                c_zip char(9),
                c_phone char(16),
                c_since timestamp,
                c_credit char(2),
                c_credit_lim numeric(12,2),
                c_discount numeric(4,4),
                c_balance numeric(12,2),
                c_ytd_payment numeric(12,2),
                c_payment_cnt int,
                c_delivery_cnt int,
                c_data varchar(500)
            ) WITH (STORAGE_TYPE=USTORE);
            create table {self.table_name}_02 (
                col_tinyint		     int,
                col_smallint	     int,
                col_int			     integer,
                col_bigint		     int,
                col_money            int,
                col_numeric		     numeric,
                col_real		     real,
                col_double		     decimal,
                col_decimal          decimal,
                col_varchar  	     varchar(100),
                col_char		     char(30),
                col_nvarchar2	     varchar(100),
                col_text		     text,
                col_timestamp		 varchar(100),
                col_timestamptz		 varchar(100),
                col_date		     date,
                col_time		     varchar(100),
                col_timetz		     varchar(100),
                col_interval	     varchar(100),
                col_tinterval        varchar(100),
                col_smalldatetine	 varchar(100),
                col_bytea			 varchar(100),
                col_boolean			 boolean,
                col_inet			 varchar(100),
                col_cidr			 varchar(100),
                col_bit				 varchar(100),
                col_varbit			 varchar(100),
                col_oid				 varchar(100) 
            ) WITH (STORAGE_TYPE=USTORE);
            create table {self.table_name}_03 (
              col_tinyint       int,
              col_smallint      int,
              col_int        integer,
              col_bigint       bigint,
              col_numeric       numeric,
              col_double       double precision,
              col_decimal          decimal,
              col_varchar        varchar(100),
              col_char       char(30),
              col_nvarchar2      nvarchar2(100),
              col_text       text,
              col_timestamptz   varchar(100),
              col_timestamp   varchar(100),
              col_interval      varchar(100),
              col_date          date
            ) WITH (STORAGE_TYPE=USTORE);
            create table {self.table_name}_04(
            c_id  varchar(30),
             c_street_1  varchar(30) not null,
             c_city  text not null,
             c_zip  varchar(30) not null,
             c_d_id  numeric not null,
             c_w_id  integer not null,
             c_since timestamp,
             c_timestamptz varchar(100),
             c_timestamp varchar(100),
             c_interval varchar(100) not null,
             c_date     varchar(100) not null
             )WITH (STORAGE_TYPE=USTORE) partition by range (c_date,c_w_id)
            (
                 PARTITION append_04_1 values less than ('20170331',5),
                 PARTITION append_04_2 values less than ('20170731',450),
                 PARTITION append_04_3 values less than ('20170930',1062),
                 PARTITION append_04_4 values less than ('20171231',1765),
                 PARTITION append_04_5 values less than ('20180331',2024),
                 PARTITION append_04_6 values less than ('20180731',2384),
                 PARTITION append_04_7 values less than ('20180930',2786),
                 PARTITION append_04_8 values less than (maxvalue,maxvalue)
            );
            create table {self.table_name}_05
            (
             d_street_1 varchar(20),
             d_city varchar(20),
             d_zip char(20) default 'djhaksdqd22d',
             d_id integer,
             d_timestamptz varchar(50),
             d_timestamp timestamp without time zone,
             d_interval varchar(100) default '2days 03:30:00',
             d_decimal decimal(10) default 988082,
             d_text text not null,
             d_numeric numeric,
             d_date date default '2015-02-15') WITH (STORAGE_TYPE=USTORE);
            CREATE TABLE {self.table_name}_06 ( 
                c_id int , 
                c_d_id char(20) , 
                c_w_id int, 
                c_first varchar(16) , 
                c_middle char(2) , 
                c_last varchar(16) , 
                c_street_1 varchar(100) , 
                c_street_2 varchar(20) , 
                c_city varchar(20) , 
                c_state char(2) , 
                c_zip char(9) , 
                c_phone char(16) , 
                c_since varchar(20) , 
                c_credit char(2) , 
                c_credit_lim numeric(12,2) , 
                c_discount decimal , 
                c_balance numeric(12,2) , 
                c_ytd_payment varchar(20) , 
                c_payment_cnt int , 
                c_delivery_cnt int , 
                c_data varchar(500)) 
            WITH (STORAGE_TYPE=USTORE) partition by range (c_id)
            (
                partition append_06_1 values less than (10),
                partition append_06_2 values less than (77),
                partition append_06_3 values less than (337),
                partition append_06_4 values less than (573),
                partition append_06_5 values less than (1357),
                partition append_06_6 values less than (2033),
                partition append_06_7 values less than (2087),
                partition append_06_8 values less than (2387),
                partition append_06_9 values less than (2687),
                partition append_06_10 values less than (2987),
                partition append_06_11 values less than (maxvalue)
            );
            CREATE TABLE {self.table_name}_07 (
            C_INT INTEGER , 
            C_SMALLINT INT , 
            C_BIGINT BIGINT , 
            C_DECIMAL DECIMAL , 
            C_NUMERIC NUMERIC , 
            C_REAL REAL , 
            C_DOUBLE DOUBLE PRECISION , 
            C_MONEY VARCHAR(20) , 
            C_CHARACTER_VARYING CHARACTER VARYING(1024) , 
            C_VARCHAR VARCHAR(20) , 
            C_CHAR CHAR(1024) , 
            C_TEXT TEXT , 
            C_BYTEA VARCHAR(20) , 
            C_TIMESTAMP_WITHOUT  VARCHAR(100) , 
            C_TIMESTAMP_WITH VARCHAR(100),
            C_BOOLEAN BOOLEAN ,  
            C_OID VARCHAR(50) ,  
            C_CHARACTER VARCHAR(50), 
            C_INTERVAL VARCHAR(50) , 
            C_DATE DATE , 
            C_TIME_WITHOUT VARCHAR(100), 
            C_TIME_WITH VARCHAR(100),
            C_binary_integer binary_integer,
            C_binary_double binary_double,
            C_dec VARCHAR(50),
            C_numeric_1 numeric(3,2),
            C_VARCHAR2 VARCHAR2(100)) WITH (STORAGE_TYPE=USTORE);
            create table {self.table_name}_08(
                    wd_tinyint       tinyint not null,
              wd_smallint      smallint not null,
              wd_int        integer not null ,
              wd_bigint       bigint,
              wd_numeric       numeric not null,
              wd_real       real,
              wd_double       double precision,
              wd_decimal          decimal,
              wd_varchar        varchar(100) not null,
              wd_char       char(30),
              wd_nvarchar2      nvarchar2(100),
              wd_text       varchar(2000),
              wd_timestamptz  varchar(100),
              wd_timestamp  varchar(100),
              wd_date       date,
              wd_time       varchar(100),
              wd_timetz   varchar(100),
              wd_interval      varchar(100) not null,
              wd_smalldatetine varchar(100),
              wd_text1            varchar(2000),
              wd_int1             integer,
              wd_varchar1         varchar(100),
              wd_num           numeric,
              wd_num1       numeric,
              wd_char1            char(20),
              wd_char2            char(20),
              wd_date4       date not null,
              wd_date5       date not null
            )
            WITH (STORAGE_TYPE=USTORE) partition by range (wd_date4)
            (
            partition order_08_1 values less than ('2014-12-01'),
            partition order_08_2 values less than ('2020-12-01'),
            partition order_08_3 values less than ('2025-12-01'),
            partition order_08_4 values less than ('2030-12-01'),
            partition order_08_5 values less than ('2035-12-01'),
            partition order_08_6 values less than ('2040-12-01'),
            partition order_08_7 values less than ('2045-12-01'),
            partition order_08_8 values less than ('2050-12-01'),
            partition order_08_9 values less than ('2055-12-01'),
            partition order_08_10 values less than (maxvalue)
            );"""
        self.log.info(cmd_sql)
        create_result = self.pri_sh.execut_db_sql(cmd_sql)
        self.log.info(create_result)
        self.assertTrue(
            create_result.count(self.constant.TABLE_CREATE_SUCCESS) == 8,
            '执行失败:' + step_txt)

        step_txt = '-----step2:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(step_txt)
        text = '-----step2.1: 复制文件到数据库主机对应目录 expect: 成功-----'
        self.log.info(text)
        self.com.scp_file(self.pri_root, self.copy_file1, self.parent_path)
        self.com.scp_file(self.pri_root, self.copy_file2, self.parent_path)
        self.com.scp_file(self.pri_root, self.copy_file3, self.parent_path)
        self.com.scp_file(self.pri_root, self.copy_file4, self.parent_path)
        self.com.scp_file(self.pri_root, self.copy_file5, self.parent_path)
        self.com.scp_file(self.pri_root, self.copy_file6, self.parent_path)
        self.com.scp_file(self.pri_root, self.copy_file7, self.parent_path)
        self.com.scp_file(self.pri_root, self.copy_file8, self.parent_path)
        chmod_cmd = f"chmod 777 -R {self.parent_path};" \
                    f"ls -al {self.parent_path}"
        self.log.info(chmod_cmd)
        chmod_result = self.pri_root.sh(chmod_cmd).result()
        self.log.info(chmod_result)

        text = '-----step2.2: 查看数据库主机目标路径下文件 expect: 成功-----'
        self.log.info(text)
        cmd = f"ls -l {self.parent_path}| grep -E '{self.copy_file1}';" \
              f"ls -l {self.parent_path}| grep -E '{self.copy_file2}';" \
              f"ls -l {self.parent_path}| grep -E '{self.copy_file3}';" \
              f"ls -l {self.parent_path}| grep -E '{self.copy_file4}';" \
              f"ls -l {self.parent_path}| grep -E '{self.copy_file5}';" \
              f"ls -l {self.parent_path}| grep -E '{self.copy_file6}';" \
              f"ls -l {self.parent_path}| grep -E '{self.copy_file7}';" \
              f"ls -l {self.parent_path}| grep -E '{self.copy_file8}';"
        self.log.info(cmd)
        res = self.pri_root.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('No such file or directory', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file1}', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file2}', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file3}', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file4}', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file5}', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file6}', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file7}', res, '文件拷贝失败')
        self.assertIn(f'{self.copy_file8}', res, '文件拷贝失败')

        text = '-----step2.3:使用copy命令插入数据，expect: 数据插入成功-----'
        self.log.info(text)
        self.log.info('---------------使用copy命令插入数据------------------')
        copy_sql = f"copy {self.table_name}_01 from '{self.target_path1}' " \
                   f"delimiters ',';" \
                   f"copy {self.table_name}_02 from '{self.target_path2}' " \
                   f"delimiters ',';" \
                   f"copy {self.table_name}_03 from '{self.target_path3}' " \
                   f"delimiters ',';" \
                   f"copy {self.table_name}_04 from '{self.target_path4}' " \
                   f"delimiters ',';" \
                   f"copy {self.table_name}_05 from '{self.target_path5}' " \
                   f"delimiters ',';" \
                   f"copy {self.table_name}_06 from '{self.target_path6}' " \
                   f"delimiters ',';" \
                   f"copy {self.table_name}_07 from '{self.target_path7}' " \
                   f"delimiters ',';" \
                   f"copy {self.table_name}_08 from '{self.target_path8}' " \
                   f"delimiters ',';"
        self.log.info(copy_sql)
        copy_result = self.pri_sh.execut_db_sql(copy_sql)
        print(copy_result)
        self.log.info(copy_result)
        self.assertIn('COPY 36000', copy_result, '执行失败:' + step_txt)
        self.assertIn('COPY 444', copy_result, '执行失败:' + step_txt)
        self.assertIn('COPY 1200', copy_result, '执行失败:' + step_txt)
        self.assertIn('COPY 9990', copy_result, '执行失败:' + step_txt)
        self.assertIn('COPY 1003', copy_result, '执行失败:' + step_txt)
        self.assertIn('COPY 30185', copy_result, '执行失败:' + step_txt)
        self.assertIn('COPY 5636', copy_result, '执行失败:' + step_txt)
        self.assertIn('COPY 4492', copy_result, '执行失败:' + step_txt)

        step_txt = '-----step3: 创建索引expect: 成功-----'
        self.log.info(step_txt)
        cmd_sql = f"""
        DROP index IF EXISTS {self.index_name}_01_1;
        DROP index IF EXISTS {self.index_name}_01_2;
        DROP index IF EXISTS {self.index_name}_01_3;
        DROP index IF EXISTS {self.index_name}_01_4;
        DROP index IF EXISTS {self.index_name}_01_6;
        DROP index IF EXISTS {self.index_name}_01_7;
        DROP index IF EXISTS {self.index_name}_01_8;
        DROP index IF EXISTS {self.index_name}_01_9;
        create index {self.index_name}_01_1 on {self.table_name}_01(c_id);
        create index {self.index_name}_01_2 on {self.table_name}_01(c_d_id);
        create index {self.index_name}_01_3 on {self.table_name}_01(c_w_id);
        create index {self.index_name}_01_4 on {self.table_name}_01(c_credit_lim);
        create index {self.index_name}_01_6 on {self.table_name}_01(c_street_2);
        create index {self.index_name}_01_7 on {self.table_name}_01(c_city);
        create index {self.index_name}_01_8 on {self.table_name}_01(c_zip);
        create index {self.index_name}_01_9 on {self.table_name}_01(c_since);
        DROP index IF EXISTS {self.index_name}_02_1;
        DROP index IF EXISTS {self.index_name}_02_2;
        DROP index IF EXISTS {self.index_name}_02_3;
        DROP index IF EXISTS {self.index_name}_02_4;
        create index {self.index_name}_02_1 on {self.table_name}_02(col_bigint);
        create index {self.index_name}_02_2 on {self.table_name}_02(col_numeric);
        create index {self.index_name}_02_3 on {self.table_name}_02(col_double);
        create index {self.index_name}_02_4 on {self.table_name}_02(col_real);
        drop index if exists {self.index_name}_04_1;
        drop index if exists {self.index_name}_04_3;
        drop index if exists {self.index_name}_04_4;
        drop index if exists {self.index_name}_04_5;
        create index {self.index_name}_04_1 on {self.table_name}_04(c_d_id,c_id)local;
        create index {self.index_name}_04_3 on {self.table_name}_04(c_id)local;
        create index {self.index_name}_04_4 on {self.table_name}_04(c_street_1)local;
        create index {self.index_name}_04_5 on {self.table_name}_04(c_zip)local;
        drop index if exists {self.index_name}_05_2;
        drop index if exists {self.index_name}_05_3;
        create index {self.index_name}_05_2 on {self.table_name}_05(d_id) ;
        create index {self.index_name}_05_3 on {self.table_name}_05(d_id,d_date);"""
        self.log.info(cmd_sql)
        create_index = self.pri_sh.execut_db_sql(cmd_sql)
        self.log.info(create_index)
        self.assertTrue(create_index.count
                        (self.constant.CREATE_INDEX_SUCCESS_MSG) == 18,
                        "执行失败：" + step_txt)

        step_txt = '-----step4: except与inner join结合 expect: 成功-----'
        self.log.info(step_txt)
        cmd_sql = f"""
select max(t6.c_id),
       case
         when t6.c_w_id < 3 then
          t6.c_w_id + 1
         else
          t6.c_w_id - 1
       end,
       1
  from {self.table_name}_05 t5 
 inner join {self.table_name}_06 t6 
    on t5.d_id = t6.c_id
   and substr(t5.d_street_1, 1, 2) = substr(t6.c_street_1, 1, 2)
 group by case
         when t6.c_w_id < 3 then
          t6.c_w_id + 1
         else
          t6.c_w_id - 1
       end
except select t2.col_int, coalesce(t3.col_tinyint, 7), 1
             from {self.table_name}_02 t2 
             full join {self.table_name}_03 t3 
               on t2.col_int = t3.col_int
              and (case
                    when t3.col_bigint < 100 then
                     t3.col_bigint
                    when t3.col_bigint >= 100 and t3.col_bigint < 200 then
                     t3.col_bigint + 1
                    else
                     t3.col_bigint + t2.col_tinyint
                  end) >= 150
              and t3.col_date between cast('2010-03-01' as date) and
                  cast('2010-05-01' as date)
            where t2.col_int < 700
              and t2.col_int > 0
            group by t2.col_int, coalesce(t3.col_tinyint, 7)
            order by 1, 2;"""
        self.log.info(cmd_sql)
        query_result = self.pri_sh.execut_db_sql(cmd_sql)
        self.log.info(query_result)
        self.assertIn('0 rows', query_result, '执行失败:' + step_txt)

    def tearDown(self):
        step_txt = '-----step5: 环境清理 expect:清理成功-----'
        self.log.info(step_txt)
        self.log.info('由于后续用例依赖表，故先不删除')
        text = '-----删除文件-----'
        self.log.info(text)
        rm_cmd = f"rm -rf {self.target_path1};" \
                 f"rm -rf {self.target_path2};" \
                 f"rm -rf {self.target_path3};" \
                 f"rm -rf {self.target_path4};" \
                 f"rm -rf {self.target_path5};" \
                 f"rm -rf {self.target_path6};" \
                 f"rm -rf {self.target_path7};" \
                 f"rm -rf {self.target_path8};"
        self.log.info(rm_cmd)
        self.com.get_sh_result(self.pri_root, rm_cmd)
        for s in [self.target_path1, self.target_path2]:
            cmd = f'if [ -f {s} ]; then echo "does exists"; ' \
                  f'else echo "not exists"; fi'
            file_judgments = self.com.get_sh_result(self.pri_root, cmd)
            self.assertIn('not exists', file_judgments, "清理失败" + text)
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
